package com.open.capacity.form.controller;


import com.open.capacity.common.FileService;
import com.open.capacity.commons.Result;
import com.open.capacity.form.service.FormDbTableService;
import com.open.capacity.form.util.KingbaseDbOperate;
import com.open.capacity.form.util.MssqlDbOperate;
import com.open.capacity.form.util.MysqlDbOperate;
import com.open.capacity.model.system.SysUser;
import com.open.capacity.utils.CollectionUtil;
import com.open.capacity.utils.DateUtil;
import com.open.capacity.utils.SysUserUtil;
import com.open.capacity.utils.file.FileUploadUtils;
import com.open.capacity.utils.file.FileUtils;
import io.swagger.annotations.ApiOperation;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.core.env.Environment;
import org.springframework.security.access.prepost.PreAuthorize;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 数据库
 *
 * @author
 * @email
 * @date 2020-08-07 09:20:08
 */
@Slf4j
@RestController
@RequestMapping("dbs")
public class DbController {

    @Value("${file.uploadpath:/usr/local/files/upload}")
    private String fileuploadPath;

    @Autowired
    private FormDbTableService tableService;

    @Autowired
    private Environment env;


    @Autowired
    private FileService fileService;

    /**
     * 获取表字段集合信息
     *
     * @param sql
     * @return
     */
    @ApiOperation(value = "获取表字段集合信息")
    @GetMapping("/columnNames")
    public Result<List<String>> getColumnNames(String sql) {
        List<Map<String, Object>> data = tableService.findData("core", "(" + sql + ") ", new HashMap<>());
        return Result.succeed(CollectionUtil.GetMapKey(data));
    }


//    /**
//     * 获取数据源数据
//     *
//     * @param sql
//     * @return
//     */
//    @ApiOperation(value = "获取数据源数据")
//    @GetMapping("/findData")
//    public PageResult<Map<String, Object>> findData(String sql, @RequestParam Map params) {
//        PageResult<Map<String, Object>> data = tableService.pageData("core", sql, params);
//        return data;
//    }

    /**
     * 获取数据源数据
     *
     * @param sql
     * @return
     */
    @ApiOperation(value = "获取数据源数据")
    @GetMapping("/findData")
    public Result<List<Map<String, Object>>> findData(String sql, @RequestParam Map params) {
        List<Map<String, Object>> data = tableService.findData("core", sql, params);
        return Result.succeed(data);
    }


    /**
     * 执行sql脚本
     */
    @RequestMapping("/excute")
    @PreAuthorize("hasAnyAuthority('dbs:db:excute')")
    public Result excute(String sql) {
        SysUser loginUser = SysUserUtil.getLoginAppUser();
        log.warn("start excute sql[{}]", sql);
        int count = tableService.excuteSql("core", sql, "");
        log.warn("end excute sql[{}], affected rows[{}]", sql, count);
        return Result.succeed("执行成功");
    }

    /**
     * 备份
     */
    @RequestMapping("/backup")
    @PreAuthorize("hasAnyAuthority('dbs:db:backup')")
    public void backup(HttpServletRequest request, HttpServletResponse response) throws Exception {
        String db = env.getProperty("spring.datasource.druid.core.driver-class-name");
        String username = env.getProperty("spring.datasource.druid.core.username");
        String password = env.getProperty("spring.datasource.druid.core.password");
        String dburl = env.getProperty("spring.datasource.druid.core.url");
        String backName = "";
        if (db.contains("mysql")) {
            //mysql
            String dbPath = env.getProperty("mysql.path"); //mysql安装目录
            int index1 = dburl.indexOf("3306") + 5;
            int index2 = dburl.indexOf("?");
            String dbname = dburl.substring(index1, index2);
            backName = dbname + "_" + DateUtil.dateTimeNow() + ".sql";
            MysqlDbOperate.dbBackUp(dbPath, username, password, dbname, fileuploadPath, backName);
        } else if (db.contains("kingbase")) {
            //kingbase
            String dbPath = env.getProperty("kingbase.path");
            int index1 = dburl.indexOf("54321") + 6;
            int index2 = dburl.indexOf("?");
            String dbname = dburl.substring(index1, index2);
            backName = dbname + "_" + DateUtil.dateTimeNow() + ".dmp";
            KingbaseDbOperate.dbBackUp(dbPath, username, password, dbname, fileuploadPath, backName);
        } else if (db.contains("sqlserver")) {
            //        } else if (db.contains("mssql")) {
            String dbPath = env.getProperty("mssql.path");
            int index1 = dburl.indexOf("databasename=") + 13;
            int index2 = dburl.indexOf(";allowMultiQueries");
            String dbname = dburl.substring(index1, index2);
            backName = dbname + "_" + DateUtil.dateTimeNow() + ".bak";
            String pathSql = fileuploadPath + "//db_bak/" + backName;
            String sql = "backup database " + dbname + " to disk='" + pathSql + "'";
            tableService.excuteSql("core",sql,"");
            //MssqlDbOperate.dbBackUp(dbPath, username, password, dbname, fileuploadPath, backName);
        }

        String filePath = fileuploadPath + "/db_bak/" + backName;
        try {
            response.setCharacterEncoding("utf-8");
            response.setContentType("multipart/form-data");
            response.setHeader("Content-Disposition",
                    "attachment;fileName=" + FileUtils.setFileDownloadHeader(request, backName));
            FileUtils.writeBytes(filePath, response.getOutputStream());
            if (false) {
                FileUtils.deleteFile(filePath);
            }
        } catch (Exception e) {
            log.error("下载文件失败", e);
        }
    }


    /**
     * 还原
     */
    @RequestMapping("/restore")
    @PreAuthorize("hasAnyAuthority('dbs:db:restore')")
    public Result restore(@RequestParam("file") MultipartFile file) throws IOException {
        String db = env.getProperty("spring.datasource.druid.core.driver-class-name");
        String username = env.getProperty("spring.datasource.druid.core.username");
        String password = env.getProperty("spring.datasource.druid.core.password");
        String dburl = env.getProperty("spring.datasource.druid.core.url");


        // 上传文件路径
        String filePath = fileuploadPath;
        // 上传并返回新文件名称
        String fileName = FileUploadUtils.upload2(filePath, file);

        if (db.contains("mysql")) {
            //mysql
            String dbPath = env.getProperty("mysql.path");
            int index1 = dburl.indexOf("3306") + 5;
            int index2 = dburl.indexOf("?");
            String dbname = dburl.substring(index1, index2);
            MysqlDbOperate.dbRestore(dbPath, username, password, dbname, fileName);
            return Result.succeed("还原成功");
        } else if (db.contains("kingbase")) {
            //kingbase
            String dbPath = env.getProperty("kingbase.path");
            int index1 = dburl.indexOf("54321") + 6;
            int index2 = dburl.indexOf("?");
            String dbname = dburl.substring(index1, index2);
            KingbaseDbOperate.dbRestore(dbPath, username, password, dbname, fileName);
            return Result.succeed("还原成功");
        } else if (db.contains("sqlserver")) {
            //sqlserver
            String dbPath = env.getProperty("mssql.path");
            String dbname = dburl.substring(dburl.indexOf("databasename=") + 13).split(";")[0];
            MssqlDbOperate.dbRestore(dbPath, username, password, dbname, fileName);
            return Result.succeed("还原成功");
        }
        return Result.succeed("还原失败");
    }

}
